import os
import re
import requests
from io import BytesIO
import pymysql
from docx import Document
from docx.shared import Pt, RGBColor, Inches
from docx.oxml.ns import qn
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.enum.text import WD_LINE_SPACING  # 引入正确的行距枚举
from PIL import Image

# 使用 pymysql 连接 MySQL 数据库
db = pymysql.connect(
    host="localhost",  # 数据库地址
    user="root",  # 数据库用户名
    password="wxmp2014",  # 数据库密码
    database="fdds",  # 数据库名
    charset='utf8mb4',  # 解决中文字符问题
    cursorclass=pymysql.cursors.DictCursor  # 返回字典类型
)

cursor = db.cursor()

# 查询books表中的所有数据
cursor.execute(
    "SELECT * FROM books LIMIT 1000"
)
books = cursor.fetchall()

# 保存文件的目录
doc_dir = "/docs/test"  # 替换为你的项目doc文件夹路径

# 确保目录存在
if not os.path.exists(doc_dir):
    os.makedirs(doc_dir)


# 定义清理文件名函数
def clean_filename(filename):
    return re.sub(r'[\\/*?:"<>|]', "", filename)


# 定义设置格式的函数
def set_font(run, font_name, font_size, is_bold=False, color=None):
    run.font.name = font_name
    run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
    run.font.size = Pt(font_size)
    run.bold = is_bold
    if color:
        run.font.color.rgb = color


# 设置样式
def set_style(doc, name="Normal", font_name='黑体', font_size=24, is_bold=True, color=None, line_spacing=28,
              first_line_indent=2, left_indent=0):
    # 创建自定义样式
    style = doc.styles.add_style(name, 1)  # 1表示标题样式
    style.font.name = 'Times New Roman'
    style._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)
    style.font.size = Pt(font_size)
    paragraph_format = style.paragraph_format
    # 设置行距
    paragraph_format.line_spacing = Pt(line_spacing)  # 设置为固定行距
    paragraph_format.line_spacing_rule = WD_LINE_SPACING.EXACTLY  # 设置固定行距规则
    # 设置首行缩进
    paragraph_format.first_line_indent = Pt(first_line_indent * 12)  # 2字符缩进
    # 设置左缩进
    paragraph_format.left_indent = Pt(left_indent)  # 设置左缩进


def insert_image(doc, urlType, bookTitle, imgUrl, height_in_inches=3):
    # 创建 img 文件夹（如果不存在）
    img_folder = os.path.join(os.getcwd(), "img")
    if not os.path.exists(img_folder):
        os.makedirs(img_folder)

    image_name = f"{clean_filename(bookTitle)}{urlType}.jpg"
    # 生成完整的图片路径
    image_path = os.path.join(img_folder, image_name)

    if os.path.exists(image_path):
        print(f"图片 {image_name} 已存在。")
    else:
        # 通过URL下载图片
        response = requests.get(imgUrl)
        if response.status_code == 200:
            # 将图片保存到本地进行验证
            image = Image.open(BytesIO(response.content))
            image = image.convert("RGB")
            image.save(image_path, "JPEG")
    try:
        # 获取图片的原始尺寸
        image = Image.open(image_path)
        original_width, original_height = image.size

        # 计算目标宽度以保持图片宽高比
        aspect_ratio = original_width / original_height
        target_height_in_inches = height_in_inches
        target_width_in_inches = target_height_in_inches * aspect_ratio

        # 插入图片并设置目标宽度和高度（以英寸为单位）
        p = doc.add_paragraph()
        run = p.add_run()
        run.add_picture(image_path, width=Inches(target_width_in_inches), height=Inches(target_height_in_inches))
        p.alignment = 1  # 图片居中
        p.paragraph_format.line_spacing = 1.0
    except Exception as e:
        print(f"Error inserting image: {e}")


# 遍历每条书籍数据并生成对应的docx文件
for book in books:
    title = clean_filename(book['title'])  # 清理标题中的非法字符
    bookCategoryName = clean_filename(book['bookCategoryName'])
    author = clean_filename(book['author'])
    speaker = clean_filename(book['speaker'])
    file_path = os.path.join(doc_dir, f"{bookCategoryName}-《{title}》-{author}-{speaker}.docx")

    # 检查文件是否已经存在
    if os.path.exists(file_path):
        print(f"文件 {file_path} 已存在，跳过生成。")
        continue
    else:
        os.makedirs(os.path.dirname(file_path), exist_ok=True)
        # 创建一个新的Word文档
        doc = Document()
        styles = doc.styles
        custom_styles = ['标题1', '标题2', '正文1']  # 自定义样式列表
        # 删除非自定义样式
        for style in styles:
            if style.name not in custom_styles:
                styles[style.name].delete()
        # 设置标题样式
        set_style(doc, '标题1', '方正小标宋简体', 24, False, "", 28, 0)
        # 设置标题样式
        set_style(doc, '标题2', '黑体', 14, False, "", 18, 0)
        # 设置正文样式
        set_style(doc, '正文1', '方正仿宋_GB2312', 12, False, "", 18, 2)
        # 页面设置
        section = doc.sections[0]
        section.top_margin = Pt(105)  # 3.7cm
        section.bottom_margin = Pt(99)  # 3.5cm
        section.left_margin = Pt(79)  # 2.8cm
        section.right_margin = Pt(73.6)  # 2.6cm
        # 公文标题（2号方正小标宋简体字，不加粗）
        # doc.add_paragraph()  # 空两行
        paragraph = doc.add_paragraph(title, style='标题1')
        paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
        if book['videoCoverUrl']:
            insert_image(doc, 'banner', book['title'], book['videoCoverUrl'], 3)
        # 正文内容
        doc.add_paragraph()  # 空两行
        doc.add_paragraph(f"作者: {book['author']}", style='标题2')
        doc.add_paragraph(f"解说: {book['speaker']}", style='标题2')
        doc.add_paragraph(f"分类: {book['bookCategoryName']}", style='标题2')
        doc.add_paragraph(f"荐语:", style='标题2')
        doc.add_paragraph(f"{book['recommendInfo']}", style='正文1')
        doc.add_paragraph(f"音频链接: \n{book['audioUrl']}", style='正文1')
        doc.add_paragraph(f"视频链接: \n{book['videoUrl']}", style='正文1')
        doc.add_paragraph(f"思维导图:", style='标题2')
        if book['mindMapUrl']:
            insert_image(doc, 'dt', book['title'], book['mindMapUrl'], 5.5)
        doc.add_paragraph(f"演说稿:", style='标题2')
        # 解说稿正文（3号仿宋GB-2312字，行距30磅）
        # 将书籍文本中的空格和 &nbsp; 都替换为换行符
        book_text_cleaned = book['bookText'].replace('&nbsp;', '\n').replace(' ', '\n')
        paragraph = doc.add_paragraph(f"\n{book_text_cleaned}", style='正文1')
        # 获取当前段落的最后一个 run
        run = paragraph.runs[0]  # 获取第一个 run，假设只有一个 run
        set_font(run, '方正仿宋_GB2312', 12)  # 设置 3 号仿宋GB-2312字
        # 设置行距为固定值28磅
        paragraph.paragraph_format.line_spacing_rule = WD_LINE_SPACING.EXACTLY  # 正确的行距规则
        paragraph.paragraph_format.line_spacing = Pt(18)  # 行距28磅
        # 保存文档，如果文件已存在则覆盖
        doc.save(file_path)

# 关闭数据库连接
cursor.close()
db.close()
